package pandora.data.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import pandora.connection.ConnectionManager;
import pandora.data.IBrandDAO;
import pandora.data.ICustomerDAO;
import pandora.data.IProductDAO;
import pandora.data.ISearchDAO;
import pandora.data.IStoreDAO;
import pandora.data.ITypeDAO;
import pandora.dto.BrandDTO;
import pandora.dto.CustomerDTO;
import pandora.dto.PriceDTO;
import pandora.dto.ProductDTO;
import pandora.dto.StoreDTO;
import pandora.dto.TypeDTO;
import pandora.utils.UIDGenerator;


public class JDBCSearchDAO implements ISearchDAO {

	private Connection conn;
	private IStoreDAO store;
	private ICustomerDAO customer;
	private IProductDAO product;
	private IBrandDAO brand;
	private ITypeDAO type;
	
	public JDBCSearchDAO(){
		conn = ConnectionManager.getInstance().checkOut();
		store = new JDBCStoreDAO();
		customer = new JDBCCustomerDAO();
		product = new JDBCProductDAO();
		brand = new JDBCBrandDAO();
		type = new JDBCTypeDAO();
	}
	
    protected void finalize() {
        ConnectionManager.getInstance().checkIn(conn);
    }

    //Métodos DCC
	public Integer countSearchedCustomerProduct(String cdni, String pbarcode) {
		PreparedStatement stmt = null;
		ResultSet result = null;
		Integer countSCP = 0;
		String customerOID = null;
		String productOID = null;
		
		String sql = "SELECT count(*) FROM Products P, Searchs S WHERE (P.OID = S.productOID) AND (S.customerOID = ?) AND (P.OID = ?) ";

		customerOID = customer.selectCustomerOIDByDNI(cdni);
		productOID = product.selectOIDOfProduct(pbarcode);
		
		try{
			
			if(customerOID != null && productOID != null){
				stmt = conn.prepareStatement(sql);
				
				stmt.setString(1,customerOID);
				stmt.setString(2,productOID);
				
				result = stmt.executeQuery();
			
				result.next();
				
				countSCP = result.getInt("count(*)");

			}
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return countSCP;
	}

	public Integer countSearchedCustomerProductBetween(String cdni,
			String pbarcode, Date d1, Date d2) {
		PreparedStatement stmt = null;
		ResultSet result = null;
		Integer countSCPB = 0;
		String customerOID = null;
		String productOID = null;
		Long time1 = new Long(0);
		Long time2 = new Long(0);
		
		String sql = "SELECT count(*) FROM Products P, Searchs S "+
        				"WHERE P.OID = S.productOID "+
        				"AND S.date BETWEEN ? AND ? "+
        				"AND S.customerOID = ? "+
        				"AND P.OID = ? ;";

		try{
			customerOID = customer.selectCustomerOIDByDNI(cdni);
			productOID = product.selectOIDOfProduct(pbarcode);
			
			if(customerOID != null && productOID != null){
                
                time1 = d1.getTime();
                time2 = d2.getTime();
				
				stmt = conn.prepareStatement(sql);
				
				stmt.setString(1,time1.toString());
				stmt.setString(2,time2.toString());
				stmt.setString(3,customerOID);
				stmt.setString(4,productOID);
				
				result = stmt.executeQuery();
			
				result.next();
				
				countSCPB = result.getInt("count(*)");

			}
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return countSCPB;
	}

	public Integer countSearchedProductAtStore(String snif, String pbarcode) {
		PreparedStatement stmt = null;
		ResultSet result = null;
		Integer countSPS = 0;
		String storeOID = null;
		String productOID = null;
		
		String sql = "SELECT count(*) FROM Products P,Searchs S,Prices R "+
        				"WHERE R.productOID = P.OID "+
        				"AND R.valid = 1 "+
        				"AND P.OID = S.productOID "+
        				"AND R.storeOID = ? "+
        				"AND P.OID = ? ;";

		try{
			storeOID = store.selectStoreOIDByNIF(snif);
			productOID = product.selectOIDOfProduct(pbarcode);
			
			if(storeOID != null && productOID != null){
				stmt = conn.prepareStatement(sql);
				stmt.setString(1,storeOID);
				stmt.setString(2,productOID);
				
				result = stmt.executeQuery();
			
				result.next();
				
				countSPS = result.getInt("count(*)");
			}
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return countSPS;
	}

	public Integer countSearchedProductAtStoreBetween(String snif,
			String pbarcode, Date d1, Date d2) {
		PreparedStatement stmt = null;
		ResultSet result = null;
		Integer countSPSB = 0;
		String storeOID = null;
		String productOID = null;
		Long time1 = new Long(0);
		Long time2 = new Long(0);
		
		String sql = "SELECT count(*) FROM Products P,Searchs S,Prices R "+
						"WHERE R.productOID = P.OID "+
						"AND R.valid = 1 "+
						"AND S.date BETWEEN ? AND ? "+
						"AND P.OID = S.productOID "+
						"AND R.storeOID = ? "+
						"AND P.OID = ? ;";

		try{
			storeOID = store.selectStoreOIDByNIF(snif);
			productOID = product.selectOIDOfProduct(pbarcode);
			
			if(storeOID != null && productOID != null){
				
				time1 = d1.getTime();
                time2 = d2.getTime();
                
				stmt = conn.prepareStatement(sql);
				
				stmt.setString(1,time1.toString());
				stmt.setString(2,time2.toString());
				stmt.setString(3,storeOID);
				stmt.setString(4,productOID);
				
				result = stmt.executeQuery();
			
				result.next();
				
				countSPSB = result.getInt("count(*)");
			}
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return countSPSB;
	}

	public List<ProductDTO> selectAllSearchedCustomerProducts(String cdni) {
		List<ProductDTO> scp = new ArrayList<ProductDTO>();
		PreparedStatement stmt = null;
		ResultSet result = null;
		String customerOID = null;
		
		String sql = "SELECT P.* FROM Products P, Searchs S "+
						"WHERE P.OID = S.productOID "+
						"AND S.customerOID = ? "+
						"GROUP BY P.barcode;";
		
		try{
			customerOID = customer.selectCustomerOIDByDNI(cdni);
			
			if(customerOID != null){
                
				stmt = conn.prepareStatement(sql);
				
				stmt.setString(1,customerOID);
				
				result = stmt.executeQuery();
			
				while(result.next()){
                    ProductDTO p = new ProductDTO();
                    String brandOID = result.getString("brandOID");
                    String typeOID = result.getString("typeOID");
                    
                    p.setBrand(brand.selectBrandByOID(brandOID));
                    p.setType(type.selectTypeByOID(typeOID));
                    p.setBarcode(result.getString("barcode"));
                    p.setProductName(result.getString("productName"));
                    p.setProductDetails(result.getString("productDetails"));
                    
                    scp.add(p);
				}
			}
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return scp;
	}

	public List<ProductDTO> selectAllSearchedCustomerProductsBetween(
			String cdni, Date d1, Date d2) {
		List<ProductDTO> scpb = new ArrayList<ProductDTO>();
		PreparedStatement stmt = null;
		ResultSet result = null;
		String customerOID = null;
		Long time1 = new Long(0);
		Long time2 = new Long(0);
		
		String sql = "SELECT P.* FROM Products P, Searchs S "+
						"WHERE P.OID = S.productOID "+
						"AND S.date BETWEEN ? AND ? "+
						"AND S.customerOID = ? "+
						"GROUP BY P.barcode;";

		try{
			customerOID = customer.selectCustomerOIDByDNI(cdni);
			
			if(customerOID != null){
				
				time1 = d1.getTime();
                time2 = d2.getTime();
                
				stmt = conn.prepareStatement(sql);
				
				stmt.setString(1,time1.toString());
				stmt.setString(2,time2.toString());
				stmt.setString(3,customerOID);
				
				result = stmt.executeQuery();
			
				while(result.next()){
                    ProductDTO p = new ProductDTO();
                    String brandOID = result.getString("brandOID");
                    String typeOID = result.getString("typeOID");
                    
                    p.setBrand(brand.selectBrandByOID(brandOID));
                    p.setType(type.selectTypeByOID(typeOID));
                    p.setBarcode(result.getString("barcode"));
                    p.setProductName(result.getString("productName"));
                    p.setProductDetails(result.getString("productDetails"));
                    
                    scpb.add(p);
				}
			}
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return scpb;
	}
	
	public List<ProductDTO> selectAllSearchedProductsAtStore(String snif) {
		List<ProductDTO> sps = new ArrayList<ProductDTO>();
		PreparedStatement stmt = null;
		ResultSet result = null;
		String storeOID = null;
		
		String sql = "SELECT P.* FROM Products P,Searchs S,Prices R "+
        				"WHERE R.productOID = P.OID "+
        				"AND R.valid = 1 "+
        				"AND P.OID = S.productOID "+
        				"AND R.storeOID = ? "+
						"GROUP BY P.barcode;";

		try{
			storeOID = store.selectStoreOIDByNIF(snif);
			
			if(storeOID != null){
                
				stmt = conn.prepareStatement(sql);
				
				stmt.setString(1,storeOID);
				
				result = stmt.executeQuery();
			
				while(result.next()){
                    ProductDTO p = new ProductDTO();
                    String brandOID = result.getString("brandOID");
                    String typeOID = result.getString("typeOID");
                    
                    p.setBrand(brand.selectBrandByOID(brandOID));
                    p.setType(type.selectTypeByOID(typeOID));
                    p.setBarcode(result.getString("barcode"));
                    p.setProductName(result.getString("productName"));
                    p.setProductDetails(result.getString("productDetails"));
                    
                    sps.add(p);
				}
			}
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return sps;
	}

	public List<ProductDTO> selectAllSearchedProductsAtStoreBetween(
			String snif, Date d1, Date d2) {
		List<ProductDTO> spsb = new ArrayList<ProductDTO>();
		PreparedStatement stmt = null;
		ResultSet result = null;
		String storeOID = null;
		Long time1 = new Long(0);
		Long time2 = new Long(0);
		
		String sql = "SELECT P.* FROM Products P,Searchs S,Prices R "+
						"WHERE R.productOID = P.OID "+
						"AND R.valid = 1 "+
						"AND S.date BETWEEN ? AND ? "+
						"AND P.OID = S.productOID "+
						"AND R.storeOID = ? "+
						"GROUP BY P.barcode;";
		
		try{
			storeOID = store.selectStoreOIDByNIF(snif);
			
			if(storeOID != null){
				
				time1 = d1.getTime();
                time2 = d2.getTime();
                
				stmt = conn.prepareStatement(sql);
				
				stmt.setString(1,time1.toString());
				stmt.setString(2,time2.toString());
				stmt.setString(3,storeOID);
				
				result = stmt.executeQuery();
				
				while(result.next()){
					ProductDTO p = new ProductDTO();
					String brandOID = result.getString("brandOID");
					String typeOID = result.getString("typeOID");
					p.setBrand(brand.selectBrandByOID(brandOID));
					p.setType(type.selectTypeByOID(typeOID));
					p.setBarcode(result.getString("barcode"));
					p.setProductName(result.getString("productName"));
					p.setProductDetails(result.getString("productDetails"));
					
					spsb.add(p);
				}
			}
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {	
			}
		}
		return spsb;
	}
	//Fin métodos DCC
	
	//Métodos MRS
	public Integer countSearchedCustomerPricesBetween(Integer coid,
			Integer poid, Date d1, Date d2) {
		// TODO Auto-generated method stub
		return null;
	}
	
	public List<PriceDTO> selectAllSearchedCustomerPricesBetween(Integer poid,
			Integer pro, Date d1, Date d2) {
		// TODO Auto-generated method stub
		return null;
	}
	//Fin métodos MRS
	
	//AVC
	
	public void saveSearch(ProductDTO product, String user, Date date) {
		
		String OID = UIDGenerator.getInstance().getKey();
		
		IProductDAO productDAO = new JDBCProductDAO();
		
		String producto= productDAO.selectOIDbyName(product.getProductName());
		
		ICustomerDAO customerDAO = new JDBCCustomerDAO();
		
		CustomerDTO customer= customerDAO.selectCustomerByAccount(user);
		String usuarioOID = customerDAO.selectCustomerOIDByDNI(customer.getCustomerDNI());
		
		Long today = System.currentTimeMillis();
		
		System.out.println(OID);
		System.out.println(producto);

		System.out.println(usuarioOID);
		System.out.println(today);
		
		String sql = "insert into Searchs (OID, productOID,customerOID,date) values ('"+OID+"','"+producto+"','"+usuarioOID+"',"+today+")";
		System.out.println(sql);		
				
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
		    System.out.println("Message: " + e.getMessage());
		    System.out.println("SQLState: " + e.getSQLState());
		    System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
		    try {
		    	if (stmt != null)
		            stmt.close();
		        } catch (SQLException e) {
		        	System.out.println("RESULTSET CLOSING ERROR OR STATEMENT CLOSING ERROR");
		        }
		    }
		}
	/*
	public List<ProductDTO> getProductsByType(StoreDTO store, TypeDTO type){
		
		PreparedStatement stmt = null;
		ResultSet result = null;
		String sql = "SELECT * FROM Products WHERE (TypeOID = ? AND NIF)";

		TypeDTO t= new TypeDTO();
		StoreDTO s= new StoreDTO();
		ProductDTO p = new ProductDTO();
		List<ProductDTO> lista= new LinkedList<ProductDTO>();

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, type.getType());
			stmt.setString(2, store.getStoreNIF());
			result = stmt.executeQuery();

			if (result.next()) {
				
				
				//t.setParentType("parentOID");
				//t.setType("OID");
				
				p.setProductName(result.getString("productName"));
				p.setProductDetails(result.getString("productDetails"));
				String oidb = result.getString("brandOID");
				String oidt = result.getString("typeOID");

				// get Brand
				p.setBrand((BrandDTO) brand.selectBrandByOID(oidb));

				// get Type
				p.setType( ((ITypeDAO) type).selectTypeByOID(oidt));
				lista.add(p);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null) {
					result.close();
				}

				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}

		return lista;
		
		//TODO
		/* 1 obtener la lista de productos de un tipo
		 * 2 recorrer la lista y sacar todos los price validos
		 * 3 encapsular en while, y en vez de recuperar, cambio de producto
		 * 
		 * */
	//}

	
	
}
